package com.bw.dao;

import com.bw.bean.Sugar;
import com.bw.bean.Sugar2;
import com.bw.bean.Sugar3;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface SugarDao {
    @Select("select tqi.question_txt `name`,\n" +
            "count(*) `counts`,\n" +
            "sum(teq.is_correct=1) `num1`,\n" +
            "sum(teq.is_correct=1)/count(*) `lv`\n" +
            "from test_exam te\n" +
            "LEFT JOIN test_paper tp ON te.paper_id=tp.id\n" +
            "LEFT JOIN test_paper_question tpq ON tp.id=tpq.paper_id\n" +
            "LEFT JOIN test_question_info tqi ON tpq.question_id=tqi.id\n" +
            "LEFT JOIN test_exam_question teq ON teq.question_id=tqi.id\n" +
            "GROUP BY tqi.question_txt")
    List<Sugar> getList2();

    @Select("select tp.paper_title `name`,\n" +
            "count(*) `counts`,\n" +
            "sum(te.score)/count(*) `avgScore`,\n" +
            "sum(te.duration_sec)/count(*) `avgTime`\n" +
            "from test_exam te \n" +
            "LEFT JOIN test_paper tp ON te.paper_id=tp.id \n" +
            "GROUP BY tp.paper_title")
    List<Sugar2> getList3();

    @Select("select bs.source_site `name`,count(oi.user_id) `value` from order_info oi \n" +
            "LEFT JOIN order_detail od ON oi.id=od.order_id\n" +
            "LEFT JOIN course_info ci ON od.course_id=ci.id\n" +
            "LEFT JOIN chapter_info ch ON ch.course_id=ci.id\n" +
            "LEFT JOIN video_info vi ON  vi.chapter_id=ch.id \n" +
            "LEFT JOIN base_source bs ON vi.video_source_id=bs.id\n" +
            "where bs.source_site is not null\n" +
            "GROUP BY bs.source_site")
    List<Sugar3> getList4();
}
